﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using BO;
namespace UIControls
{
    /*
在以往读取Excel文件时常常出现读取某些字段为null值，其实是有值，原因是读取文件时，Excel会以第一行的数据类型为参考，如果后边的与其不一致，则会出现些问题。
　　以下一个朋友的解决方案C读excel出现null原因 - readwall - 乡间小路，他的Excel文件的连接串写作如下。据说可以强制数据为字符串，具体我没有再测试。仅供参考。
　　string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
　　其中红色的部分比较值得注意，HDR和IMEX一定要配合使用，HDR表示要把第一行作为数据还是作为列名，作为数据用hdr=no,作为列名用 hdr=yes；通过Imex=1来把混合型作为文本型读取，避免null值。SSIS中的Excel Connection和其他的Connection不一样，用户不需要指定数据类型，Excel的driver可以先去做一个采样，然后根据结果去猜一下数据类型。
 * 
 * */
    public class ExcelAdapter
    {
        public const string ExcelFilter = "Excel files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx";

        /// <summary>
        /// 单独的一个方法.用于导出数据到Excel中 直接调用即可.
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dtFilter"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetName">不可用的(默认是sheet1)</param>
        public void SaveExcel(DataTable dt, string dtFilter, string fileName, string sheetName)
        {
            SaveFileDialog a = new SaveFileDialog();
            a.FileName = fileName;
            a.Filter = ExcelFilter;
            if (a.ShowDialog() == DialogResult.OK)
            {
                sheetName = "sheet1";
                fileName = a.FileName;
            }
            else
            {
                return;
            }

            try
            {
                System.IO.File.Delete(fileName);
            }
            catch (ExceptionEx)
            {
                throw new ExceptionEx("该文件已经存在，删除文件时出错！");
            }

            string ConnStr;
            ConnStr = GenerateConnectString4Save(fileName);

            OleDbConnection conn_excel = new OleDbConnection();
            conn_excel.ConnectionString = ConnStr;

            OleDbCommand cmd_excel = new OleDbCommand();

            string sql;
            sql = SqlCreate(dt, sheetName);

            conn_excel.Open();
            cmd_excel.Connection = conn_excel;
            cmd_excel.CommandText = sql;
            cmd_excel.ExecuteNonQuery();

            conn_excel.Close();

            OleDbDataAdapter da_excel = new OleDbDataAdapter("Select * From [" + sheetName + "$]", conn_excel);
            DataTable dt_excel = new DataTable();
            da_excel.Fill(dt_excel);

            da_excel.InsertCommand = SqlInsert(sheetName, dt, conn_excel);

            System.Data.DataRow dr_excel;
            string ColumnName;

            foreach (System.Data.DataRow dr in dt.Select(dtFilter))
            {
                dr_excel = dt_excel.NewRow();

                foreach (DataColumn dc in dt.Columns)
                {
                    ColumnName = dc.ColumnName;

                    dr_excel[dc.ColumnName] = dr[ColumnName];

                }
                dt_excel.Rows.Add(dr_excel);

            }
            da_excel.ContinueUpdateOnError = true;
            da_excel.Update(dt_excel);

            conn_excel.Close();

            if (MessageBox.Show("数据成功导出到『" + fileName + "』，是否现在打开？", "导出",
                MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(fileName);
            }
        }

        private void CheckColumn(DataTable dt, DataTable dt_v)
        {
            foreach (System.Data.DataRow dr in dt_v.Select())
            {
                if (!dt.Columns.Contains(dr["列名"].ToString()))
                {
                    dr.Delete();
                }
            }
            dt_v.AcceptChanges();
        }

        private string GetDataType(Type i)
        {
            string s;

            switch (i.Name)
            {
                case "String":
                    s = "Char";
                    break;
                case "Int32":
                    s = "Int";
                    break;
                case "Int64":
                    s = "Int";
                    break;
                case "Int16":
                    s = "Int";
                    break;
                case "Double":
                    s = "Double";
                    break;
                case "Decimal":
                    s = "Double";
                    break;
                default:
                    s = "Char";
                    break;

            }
            return s;
        }

        private OleDbType StringToOleDbType(Type i)
        {
            OleDbType s;

            switch (i.Name)
            {
                case "String":
                    s = OleDbType.Char;
                    break;
                case "Int32":
                    s = OleDbType.Integer;
                    break;
                case "Int64":
                    s = OleDbType.Integer;
                    break;
                case "Int16":
                    s = OleDbType.Integer;
                    break;
                case "Double":
                    s = OleDbType.Double;
                    break;
                case "Decimal":
                    s = OleDbType.Decimal;
                    break;
                default:
                    s = OleDbType.Char;
                    break;

            }
            return s;

        }


        private string SqlCreate(DataTable dt, string SheetName)
        {
            string sql;

            sql = "CREATE TABLE " + SheetName + " (";

            foreach (DataColumn dc in dt.Columns)
            {
                sql += "[" + dc.ColumnName + "] " + GetDataType(dc.DataType) + " ,";
            }

            //sql = "CREATE TABLE [" + SheetName + "] (";

            //foreach (C1.Win.C1TrueDBGrid.C1DataColumn dc in grid.Columns)
            //{
            //    sql += "[" + dc.Caption + "] " + GetDataType(dc.DataType) + ",";
            //}
            //sql = sql.Substring(0, sql.Length - 1);
            //sql += ")";

            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";

            return sql;
        }


        // 生成 InsertCommand 并设置参数
        private OleDbCommand SqlInsert(string SheetName, DataTable dt, OleDbConnection conn_excel)
        {
            OleDbCommand i;
            string sql;

            sql = "INSERT INTO [" + SheetName + "$] (";
            foreach (DataColumn dc in dt.Columns)
            {
                sql += "[" + dc.ColumnName + "] ";
                sql += ",";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ") VALUES (";
            foreach (DataColumn dc in dt.Columns)
            {
                sql += "?,";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";

            i = new OleDbCommand(sql, conn_excel);

            foreach (DataColumn dc in dt.Columns)
            {
                i.Parameters.Add("@" + dc.Caption, StringToOleDbType(dc.DataType), 0, dc.Caption);
            }

            return i;
        }

        string GenerateConnectString4Save(string fileName)
        {
            var arr = fileName.Split('.');
            var extName = arr[arr.Length - 1].ToLower();
            switch (extName)
            {
                case "xlsx":
                    return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES'";
                case "xls":
                    return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + fileName + "\";Extended Properties=\"Excel 8.0;HDR=YES\"";
            }
            throw new ExceptionEx("The file extension name is not excpted.");

        }

        string GenerateConnectString4Open(string fileName)
        {
            var arr = fileName.Split('.');
            var extName = arr[arr.Length - 1].ToLower();
            switch (extName)
            {
                case "xlsx":
                    return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
                case "xls":
                    return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + fileName + "\";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            }
            throw new ExceptionEx("The file extension name is not excpted.");

        }
        #region Variable Declarations

        /// <summary>
        /// Current message from the client
        /// </summary>
        string m_CurrentMessage = "";

        /// <summary>
        /// The file path for the source excel book
        /// </summary>
        string m_SourceFileName;

        /// <summary>
        /// Connects to the source excel workbook
        /// </summary>
        OleDbConnection m_ConnectionToExcelBook;

        /// <summary>
        /// Reads the data from the document to a System.Data object
        /// </summary>
        OleDbDataAdapter m_AdapterForExcelBook;

        #endregion

        #region Constructor Logic

        /// <summary>
        /// Parameterized constructor .. specifies path
        /// </summary>
        /// <param name="iSourceFileName">The source filename</param>
        public ExcelAdapter(string iSourceFileName)
        {

            this.m_SourceFileName = iSourceFileName;

        }
        public ExcelAdapter()
        {
        }

        #endregion

        #region Properties

        /// <summary>
        /// Gets he current messages from the client
        /// </summary>
        public string CurrentMessage
        {

            get
            {
                return this.m_CurrentMessage;
            }

        }

        /// <summary>
        /// Property that gets / sets the current source excel book
        /// </summary>
        public string FileName
        {

            get
            {
                return this.m_SourceFileName;
            }
            set
            {
                this.m_SourceFileName = value;
            }

        }

        #endregion

        #region Methods

        /// <summary>
        /// Runs a non query database command such as UPDATE , INSERT or DELETE
        /// </summary>
        /// <param name="iQuery">The required query</param>
        /// <returns></returns>
        public bool runNonQuery(string iQuery)
        {

            try
            {


                OleDbCommand nonQueryCommand = new OleDbCommand(iQuery);

                nonQueryCommand.Connection = this.m_ConnectionToExcelBook;
                nonQueryCommand.CommandText = iQuery;

                int rowsAffected = nonQueryCommand.ExecuteNonQuery();

                this.m_CurrentMessage = "SUCCESS - " + rowsAffected.ToString() + " Rows affected ";


                return true;

            }
            catch (ExceptionEx ex)
            {

                this.m_CurrentMessage = "ERROR " + ex.Message;
                throw new ExceptionEx("ExcelAdapter Error", ex);
            }


        }

        /// <summary>
        /// Reads data as per the user query
        /// </summary>
        /// <param name="iQuery">The speicfic Query</param>
        /// <returns></returns>
        public DataTable readForSpecificQuery(string iQuery)
        {


            try
            {

                DataTable returnDataObject = new DataTable();

                OleDbCommand selectCommand = new OleDbCommand(iQuery);
                selectCommand.Connection = this.m_ConnectionToExcelBook;

                this.m_AdapterForExcelBook = new OleDbDataAdapter();


                this.m_AdapterForExcelBook.SelectCommand = selectCommand;
                this.m_AdapterForExcelBook.Fill(returnDataObject);

                this.m_CurrentMessage = "SUCCESS - " + returnDataObject.Rows.Count + " Records Loaded ";

                return returnDataObject;

            }
            catch (ExceptionEx ex)
            {

                this.m_CurrentMessage = "ERROR " + ex.Message;
                throw new ExceptionEx("ExcelAdapter Error", ex);
            }


        }

        /// <summary>
        /// Reads an entire excel sheet from an opened excel workbook
        /// </summary>
        /// <param name="iSheetName"></param>
        /// <returns></returns>
        public DataTable readEntireSheet()
        {

            try
            {

                DataTable dt = m_ConnectionToExcelBook.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                DataTable returnDataObject = new DataTable();
                foreach (DataRow row in dt.Rows)
                {
                    string iSheetName = row["TABLE_NAME"].ToString();

                   

                    OleDbCommand selectCommand = new OleDbCommand("select * from [" + iSheetName + "]");
                    selectCommand.Connection = this.m_ConnectionToExcelBook;
                    this.m_AdapterForExcelBook = new OleDbDataAdapter();


                    this.m_AdapterForExcelBook.SelectCommand = selectCommand;
                    this.m_AdapterForExcelBook.Fill(returnDataObject);
                    if (returnDataObject.Rows.Count > 0)
                        break;

                }

                this.m_CurrentMessage = "SUCCESS - " + returnDataObject.Rows.Count + " Records Loaded ";

                return returnDataObject;

            }
            catch (ExceptionEx ex)
            {
                this.m_CurrentMessage = "ERROR " + ex.Message;
                throw new ExceptionEx("ExcelAdapter Error", ex);
            }
            

        }

       /// <summary>
       /// Return All Tables in the Excel File.
       /// </summary>
       /// <returns></returns>
        public DataSet readAllEntireSheet()
        {

            try
            {

                DataTable dt = m_ConnectionToExcelBook.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                var returnObj = new DataSet();
                foreach (DataRow row in dt.Rows)
                {
                    string iSheetName = row["TABLE_NAME"].ToString();
                    if (!iSheetName.EndsWith("$")) continue;
                    DataTable dt1 = new DataTable(iSheetName.Trim('$'));

                    OleDbCommand selectCommand = new OleDbCommand("select * from [" + iSheetName + "]");
                    selectCommand.Connection = this.m_ConnectionToExcelBook;
                    this.m_AdapterForExcelBook = new OleDbDataAdapter();


                    this.m_AdapterForExcelBook.SelectCommand = selectCommand;
                    this.m_AdapterForExcelBook.Fill(dt1);

                    returnObj.Tables.Add(dt1);
                    
                }
                return returnObj; ;

            }
            catch (ExceptionEx ex)
            {
                this.m_CurrentMessage = "ERROR " + ex.Message;
                throw new ExceptionEx("ExcelAdapter Error", ex);
            }


        }

        /// <summary>
        /// Opens the connection to the source excel document
        /// </summary>
        /// <returns></returns>
        public bool openConnection()
        {


            try
            {


                this.m_ConnectionToExcelBook = new OleDbConnection();

                m_ConnectionToExcelBook.ConnectionString = GenerateConnectString4Open(m_SourceFileName);

                this.m_ConnectionToExcelBook.Open();

                this.m_CurrentMessage = "SUCCESS - Connection to Source Established";


            }
            catch (ExceptionEx ex)
            {

                this.m_CurrentMessage = "ERROR " + ex.Message;
           
                throw new ExceptionEx("Error Opening Source", ex);
            }

            return true;

        }


        /// <summary>
        /// Closes the connection to the source excel document
        /// </summary>
        /// <returns></returns>
        public bool closeConnection()
        {


            try
            {


                this.m_ConnectionToExcelBook.Close();

                this.m_CurrentMessage = "SUCCESS - Connection to Source Closed";


            }
            catch (ExceptionEx ex)
            {

                this.m_CurrentMessage = "ERROR " + ex.Message;
                throw new ExceptionEx(m_CurrentMessage+"关闭连接失败.");

            }

            return true;

        }



        #endregion



    }

}
